create database QuangCaoABC
go

use QuangCaoABC
go

create table KhachHang
(
	MaKhachHang int,
	TenKhachHang nvarchar(50),
	DiaChi nvarchar(50),
	SoDT int,
	primary key(MaKhachHang)
)
go
create table QuangCao
(
	MaQuangCao int,
	ChieuDai int,
	KichThuoc varchar(50),
	NgayDang datetime,
	TrangDang int,
	MaKhachHang int,
	primary key(MaQuangCao)
)
go
create table NhanVien
(
	MaNhanVien int,
	TenNhanVien nvarchar(50),
	DiaChi nvarchar(50),
	SoDT int,
	TenDangNhap nvarchar(20),
	MatKhau varchar(20),
	MaNhanVienQuanLy int,
	primary key(MaNhanVien)
)
go
create table HopDong
(
	MaHopDong int,
	TenHopDong  nvarchar(50),
	NgayKy datetime,
	MaKhachHang int,
	primary key(MaHopDong)
)
go
create table ChiTietHD
(
	MaCTHopDong int,
	TienDatCoc float,
	NgayDang datetime,
	SoLanThanhToan int,
	SoTienMoiLanThanhToan float,
	TinhTrangHopDong nvarchar(50),
	TongTien float,
	MaHopDong int,
	MaNVQuanLy int,
	primary key(MaCTHopDong)
)
go
create table MaLoaiQuangCao
(
	MaLoaiQuangCao int,
	MoTa nvarchar(100),
	ChieuDai float,
	KichThuoc varchar(50),
	SoTrang int,
	GiaTien double,
	MaQuangCao int,
	primary key(MaLoaiQuangCao)
)

go
alter table QuangCao
	add constraint fk_QC_KH foreign key (MaKhachHang) references KhachHang (MaKhachHang)
alter table NhanVien
	add constraint fk_NV_NV foreign key (MaNhanVienQuanLy) references NhanVien (MaNhanVien)
alter table ChiTietHD
	add constraint fk_CTHD_HD foreign key (MaHopDong) references HopDong (MaHopDong),
	constraint fk_CTHD_NV foreign key (MaNVQuanLy) references NhanVien (MaNhanVien)
alter table HopDong
	add constraint fk_HD_KH foreign key (MaKhachHang) references KhachHang (MaKhachHang)
alter table MaLoaiQuangCao
	add constraint fk_LQC_QC foreign key (MaQuangCao) references QuangCao (MaQuangCao)


use master 
go
drop database QuangCaoABC